package com.school.dao.impl;

import com.school.config.DbcpConfig;
import com.school.dao.StudentDao;
import com.school.dao.UserDao;
import com.school.model.Role;
import com.school.model.Student;
import com.school.model.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

public class UserDaoImpl extends BaseDaoImpl<User> implements UserDao {

    @Override
    public User insert(User user) {
        String sql = "insert into " + tableName + " (nickname,username,password) value (?,?,?)";
        try {
            // 创建一个PreparedStatement对象，用于执行一次查询过程
            PreparedStatement statement = DbcpConfig.getPreparedStatement(sql);
            if (statement != null) {
                statement.setString(1, user.getNickname());
                statement.setString(2, user.getUsername());
                statement.setString(3, user.getPassword());
                // 执行上面的sql语句取查询，得到查询的条数
                statement.executeUpdate();
                // 获取最后操作的id集合
                ResultSet keys = statement.getGeneratedKeys();
                // 放到student对象中
                while (keys.next()) {
                    user.setId(keys.getInt(1));
                }
                // 关闭查询过程
                statement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        // 返回插入的条数
        return user;
    }

    @Override
    public List<User> insert(List<User> users) {
        String sql = "insert into " + tableName + " (nickname,username,password) values (?,?,?)";
        try {
            // 获取连接对象
            Connection connection = DbcpConfig.getConnection();
            // 开启事务
            connection.setAutoCommit(false);
            // 创建一个PreparedStatement对象，用于执行一次查询过程
            PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if (statement != null) {
                for (User user : users) {
                    statement.setString(1, user.getNickname());
                    statement.setString(2, user.getUsername());
                    statement.setString(3, user.getPassword());
                    // 正常情况需要提交，但是现在暂存一次操作
                    statement.addBatch();
                }
                // 再一次性全部提交
                statement.executeBatch();
                // 获取最后操作的id集合
                ResultSet keys = statement.getGeneratedKeys();
                // 放到student对象中
                for (User user : users) {
                    keys.next();
                    if (keys != null) {
                        user.setId(keys.getInt(1));
                    }
                }
                // 关闭查询过程
                statement.close();
                connection.commit();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        // 返回插入的条数
        return users;
    }

    @Override
    public int update(User user) {
        // update student set stunum='fjsjklf', sex='女' wehere id=1
        StringBuffer sql = new StringBuffer("update " + tableName + " set ");
        if (user.getNickname() != null) {
            sql.append(" nickname = '" + user.getNickname() + "',");
        }
        if (user.getUsername() != null) {
            sql.append(" username = '" + user.getUsername() + "',");
        }
        if (user.getPassword() != null) {
            sql.append(" password = '" + user.getPassword() + "',");
        }
        // 获取”,“最后一次出现的位置下标
        int lastIndex = sql.lastIndexOf(",");
        // 删除最后一个”,“
        sql.delete(lastIndex, lastIndex + 1);
        sql.append(" where id = " + user.getId());
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql.toString());
            if (preparedStatement != null) {
                return preparedStatement.executeUpdate();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int[] update(List<User> users) {
        try {
            Connection connection = DbcpConfig.getConnection();
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            if (statement != null) {
                for (User user : users) {
                    // update student set stunum='fjsjklf', sex='女' wehere id=1
                    StringBuffer sql = new StringBuffer("update " + tableName + " set ");
                    if (user.getNickname() != null) {
                        sql.append(" nickname = '" + user.getNickname() + "',");
                    }
                    if (user.getUsername() != null) {
                        sql.append(" username = '" + user.getUsername() + "',");
                    }
                    if (user.getPassword() != null) {
                        sql.append(" password = '" + user.getPassword() + "',");
                    }
                    // 获取”,“最后一次出现的位置下标
                    int lastIndex = sql.lastIndexOf(",");
                    // 删除最后一个”,“
                    sql.delete(lastIndex, lastIndex + 1);
                    sql.append(" where id = " + user.getId());
                    System.out.println(sql);
                    statement.addBatch(sql.toString());
                }
                // 返回成功和失败：1-成功；0-失败
                int res[] = statement.executeBatch();
                connection.commit();
                statement.close();
                return res;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new int[0];
    }

    @Override
    public User findOneById(Integer id) {
        String sql = "select id,nickname,username,password from " + tableName + " where id = ?";
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql);
            if (preparedStatement != null) {
                preparedStatement.setInt(1, id);
                ResultSet resultSet = preparedStatement.executeQuery();
                User user = new User();
                while (resultSet.next()) {
                    user.setId(resultSet.getInt("id"));
                    user.setNickname(resultSet.getString("nickname"));
                    user.setUsername(resultSet.getString("username"));
                    user.setPassword(resultSet.getString("password"));
                }
                preparedStatement.close();
                return user;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new User();
    }

    @Override
    public List<User> findPage(User user, Integer offset, Integer size) {
        // select * from student limit 0,10;
        StringBuffer sql = new StringBuffer("select id,nickname,username,password from ");
        sql.append(tableName).append(" where ");
        if (user.getUsername() != null) {
            sql.append(" nickname like '%" + user.getNickname() + "%' or ");
        }
        if (user.getUsername() != null) {
            sql.append(" username like '%" + user.getUsername() + "%' or ");
        }
        // 获取”,“最后一次出现的位置下标
        int lastIndex = sql.lastIndexOf("or");
        // 删除最后一个”,“
        sql.delete(lastIndex, lastIndex + 2);
        sql.append(" limit ?,?");
        System.out.println(sql.toString());
        List<User> list = new ArrayList<>();
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql.toString());
            if (preparedStatement != null) {
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, size);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    User user1 = new User();
                    user1.setId(resultSet.getInt("id"));
                    user1.setNickname(resultSet.getString("nickname"));
                    user1.setUsername(resultSet.getString("username"));
                    user1.setPassword(resultSet.getString("password"));
                    list.add(user1);
                }
                preparedStatement.close();
                return list;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public Integer signIn(String username, String password) {
        String sql = "select id from " + tableName + " where username = ? and password = ?";
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql);
            if (preparedStatement != null) {
                preparedStatement.setString(1, username);
                preparedStatement.setString(2, password);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    return resultSet.getInt("id");
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Integer> getRoleIdsByUserId(Integer userId) {
        String sql = "select role_id from user_role where user_id = ?";
        try {
            PreparedStatement preparedStatement = DbcpConfig.getPreparedStatement(sql);
            // 根据用户id查询角色id
            List<Integer> ids = new LinkedList<>();
            if (preparedStatement != null) {
                preparedStatement.setInt(1, userId);
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    ids.add(resultSet.getInt("role_id"));
                }
            }
            preparedStatement.close();
            return ids;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Role> getRolesByIds(List<Integer> roleIds) {
        String sql = "select id,rolename,note from role where id = ?";
        // 通过角色id查询角色信息
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = DbcpConfig.getPreparedStatement(sql);
            List<Role> roles = new LinkedList<>();
            for (Integer id : roleIds) {
                if (preparedStatement != null) {
                    preparedStatement.setInt(1, id);
                    ResultSet resultSet = preparedStatement.executeQuery();
                    while (resultSet.next()) {
                        Role role = new Role();
                        role.setId(resultSet.getInt("id"));
                        role.setRolename(resultSet.getString("rolename"));
                        role.setNote(resultSet.getString("note"));
                        roles.add(role);
                    }
                }
            }
            return roles;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
